﻿using System;
using System.Collections;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MySql.Data;
using MySql.Data.MySqlClient;
using System.Data;
using System.Windows.Forms;
using thinger.VisitionDAL;
using System.Collections.Generic;

namespace thinger.VisionDemo
{
	///   <summary>
	/// MYSQLHelper 的摘要说明
	///   </summary>
	public abstract class MySqlHelper
	{
		/*
            Database='wp'：指定要连接的数据库名称为"wp"。
            Data Source='localhost'：指定数据源为本地主机。
            User Id='root'：指定用户名为"root"。
            Password='root'：指定密码为"root"。
            charset='utf8'：指定字符集为UTF-8。
            pooling=true：设置是否启用连接池，即复用已存在的数据库连接对象。

         */

		// 数据库连接字符串
		public static string Conn = " Database='xktdmcdatabase';Data Source='localhost';User Id='root';Password='123456';charset='utf8';pooling=true ";

		//  用于缓存参数的HASH表
		private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable());

		///   <summary>
		///   给定连接的数据库用假设参数执行一个sql命令（不返回数据集）
		///   </summary>
		///   <param name="connectionString"> 一个有效的连接字符串 </param>
		///   <param name="cmdType"> 命令类型(存储过程, 文本, 等等) </param>
		///   <param name="cmdText"> 存储过程名称或者sql命令语句 </param>
		///   <param name="commandParameters"> 执行命令所用参数的集合 </param>
		///   <returns> 执行命令所影响的行数 </returns>
		public static int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters)
		{
			MySqlCommand cmd = new MySqlCommand();

			using (MySqlConnection conn = new MySqlConnection(connectionString))
			{
				PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
				int val = cmd.ExecuteNonQuery();
				cmd.Parameters.Clear();
				return val;
			}
		}

		///   <summary>
		///  用现有的数据库连接执行一个sql命令（不返回数据集）
		///   </summary>
		///   <param name="connection"> 一个现有的数据库连接 </param>
		///   <param name="cmdType"> 命令类型(存储过程, 文本, 等等) </param>
		///   <param name="cmdText"> 存储过程名称或者sql命令语句 </param>
		///   <param name="commandParameters"> 执行命令所用参数的集合 </param>
		///   <returns> 执行命令所影响的行数 </returns>
		public static int ExecuteNonQuery(MySqlConnection connection, CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters)
		{
			MySqlCommand cmd = new MySqlCommand();

			PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
			int val = cmd.ExecuteNonQuery();
			cmd.Parameters.Clear();
			return val;
		}

		///   <summary>
		/// 使用现有的SQL事务执行一个sql命令（不返回数据集）
		///   </summary>
		///   <remarks>
		/// 举例:
		///   int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new MySqlParameter("@prodid", 24));
		///   </remarks>
		///   <param name="trans"> 一个现有的事务 </param>
		///   <param name="cmdType"> 命令类型(存储过程, 文本, 等等) </param>
		///   <param name="cmdText"> 存储过程名称或者sql命令语句 </param>
		///   <param name="commandParameters"> 执行命令所用参数的集合 </param>
		///   <returns> 执行命令所影响的行数 </returns>
		public static int ExecuteNonQuery(MySqlTransaction trans, CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters)
		{
			MySqlCommand cmd = new MySqlCommand();
			PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters);
			int val = cmd.ExecuteNonQuery();
			cmd.Parameters.Clear();
			return val;
		}

		///   <summary>
		///  用执行的数据库连接执行一个返回数据集的sql命令
		///   </summary>
		///   <remarks>
		///  举例:
		///   MySqlDataReader r = ExecuteReader(connString, CommandType.StoredProcedure, "PublishOrders", new MySqlParameter("@prodid", 24));
		///   </remarks>
		///   <param name="connectionString"> 一个有效的连接字符串 </param>
		///   <param name="cmdType"> 命令类型(存储过程, 文本, 等等) </param>
		///   <param name="cmdText"> 存储过程名称或者sql命令语句 </param>
		///   <param name="commandParameters"> 执行命令所用参数的集合 </param>
		///   <returns> 包含结果的读取器 </returns>
		public static MySqlDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters)
		{
			// 创建一个MySqlCommand对象
			MySqlCommand cmd = new MySqlCommand();
			// 创建一个MySqlConnection对象
			MySqlConnection conn = new MySqlConnection(connectionString);

			// 在这里我们用一个try/catch结构执行sql文本命令/存储过程，因为如果这个方法产生一个异常我们要关闭连接，因为没有读取器存在，
			// 因此commandBehaviour.CloseConnection 就不会执行
			try
			{
				// 调用 PrepareCommand 方法，对 MySqlCommand 对象设置参数
				PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
				// 调用 MySqlCommand  的 ExecuteReader 方法
				MySqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
				// 清除参数
				cmd.Parameters.Clear();
				return reader;
			}
			catch
			{
				// 关闭连接，抛出异常
				conn.Close();
				throw;
			}
		}

		///   <summary>
		///  返回DataSet
		///   </summary>
		///   <param name="connectionString"> 一个有效的连接字符串 </param>
		///   <param name="cmdType"> 命令类型(存储过程, 文本, 等等) </param>
		///   <param name="cmdText"> 存储过程名称或者sql命令语句 </param>
		///   <param name="commandParameters"> 执行命令所用参数的集合 </param>
		///   <returns></returns>
		public static DataSet GetDataSet(string connectionString, CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters)
		{
			// 创建一个MySqlCommand对象
			MySqlCommand cmd = new MySqlCommand();
			// 创建一个MySqlConnection对象
			MySqlConnection conn = new MySqlConnection(connectionString);

			// 在这里我们用一个try/catch结构执行sql文本命令/存储过程，因为如果这个方法产生一个异常我们要关闭连接，因为没有读取器存在，

			try
			{
				// 调用 PrepareCommand 方法，对 MySqlCommand 对象设置参数
				PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
				// 调用 MySqlCommand  的 ExecuteReader 方法
				MySqlDataAdapter adapter = new MySqlDataAdapter();
				adapter.SelectCommand = cmd;

				DataSet ds = new DataSet();

				adapter.Fill(ds, "表格");
				// 清除参数
				cmd.Parameters.Clear();
				conn.Close();
				return ds;
			}
			catch (Exception e)
			{
				throw e;
			}
		}

		///   <summary>
		///  返回DataSet
		///   </summary>
		///   <param name="connectionString"> 一个有效的连接字符串 </param>
		///   <param name="cmdType"> 命令类型(存储过程, 文本, 等等) </param>
		///   <param name="cmdText"> 存储过程名称或者sql命令语句 </param>
		///   <param name="commandParameters"> 执行命令所用参数的集合 </param>
		///   <returns></returns>
		public static DataTable GetDataTable(string connectionString, CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters)
		{
			// 创建一个MySqlCommand对象
			MySqlCommand cmd = new MySqlCommand();
			// 创建一个MySqlConnection对象
			MySqlConnection conn = new MySqlConnection(connectionString);

			// 在这里我们用一个try/catch结构执行sql文本命令/存储过程，因为如果这个方法产生一个异常我们要关闭连接，因为没有读取器存在，

			try
			{
				// 调用 PrepareCommand 方法，对 MySqlCommand 对象设置参数
				PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
				// 调用 MySqlCommand  的 ExecuteReader 方法
				MySqlDataAdapter adapter = new MySqlDataAdapter();
				adapter.SelectCommand = cmd;

				DataTable ds = new DataTable();

				adapter.Fill(ds);
				// 清除参数
				cmd.Parameters.Clear();
				conn.Close();
				return ds;
			}
			catch (Exception e)
			{
				throw e;
			}
		}

		public static void ShowData(string connectionString, CommandType cmdType, string cmdText, ref DataGridView dt, params MySqlParameter[] commandParameters)
		{
			// 创建一个MySqlCommand对象
			MySqlCommand cmd = new MySqlCommand();
			// 创建一个MySqlConnection对象
			MySqlConnection conn = new MySqlConnection(connectionString);

			// 在这里我们用一个try/catch结构执行sql文本命令/存储过程，因为如果这个方法产生一个异常我们要关闭连接，因为没有读取器存在，

			try
			{
				// 调用 PrepareCommand 方法，对 MySqlCommand 对象设置参数
				PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
				// 调用 MySqlCommand  的 ExecuteReader 方法
				MySqlDataAdapter adapter = new MySqlDataAdapter();
				adapter.SelectCommand = cmd;

				DataSet ds = new DataSet();
				adapter.Fill(ds, "表格");

				dt.DataSource = ds;
				dt.DataMember = "表格";
				// 清除参数
				cmd.Parameters.Clear();
				conn.Close();
				//return ds;
			}
			catch (Exception e)
			{
				throw e;
			}
		}

		///   <summary>
		///  用指定的数据库连接字符串执行一个命令并返回一个数据集的第一列
		///   </summary>
		///   <remarks>
		/// 例如:
		///   Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new MySqlParameter("@prodid", 24));
		///   </remarks>
		/// <param name="connectionString"> 一个有效的连接字符串 </param>
		///   <param name="cmdType"> 命令类型(存储过程, 文本, 等等) </param>
		///   <param name="cmdText"> 存储过程名称或者sql命令语句 </param>
		///   <param name="commandParameters"> 执行命令所用参数的集合 </param>
		///   <returns> 用 Convert.To{Type}把类型转换为想要的  </returns>
		public static object ExecuteScalar(string connectionString, CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters)
		{
			MySqlCommand cmd = new MySqlCommand();

			using (MySqlConnection connection = new MySqlConnection(connectionString))
			{
				PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
				object val = cmd.ExecuteScalar();
				cmd.Parameters.Clear();
				return val;
			}
		}

		///   <summary>
		///  用指定的数据库连接执行一个命令并返回一个数据集的第一列
		///   </summary>
		///   <remarks>
		///  例如:
		///   Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new MySqlParameter("@prodid", 24));
		///   </remarks>
		///   <param name="connection"> 一个存在的数据库连接 </param>
		///   <param name="cmdType"> 命令类型(存储过程, 文本, 等等) </param>
		///   <param name="cmdText"> 存储过程名称或者sql命令语句 </param>
		///   <param name="commandParameters"> 执行命令所用参数的集合 </param>
		///   <returns> 用 Convert.To{Type}把类型转换为想要的  </returns>
		public static object ExecuteScalar(MySqlConnection connection, CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters)
		{
			MySqlCommand cmd = new MySqlCommand();

			PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
			object val = cmd.ExecuteScalar();
			cmd.Parameters.Clear();
			return val;
		}

		///   <summary>
		///  将参数集合添加到缓存
		///   </summary>
		///   <param name="cacheKey"> 添加到缓存的变量 </param>
		///   <param name="commandParameters"> 一个将要添加到缓存的sql参数集合 </param>
		public static void CacheParameters(string cacheKey, params MySqlParameter[] commandParameters)
		{
			parmCache[cacheKey] = commandParameters;
		}

		///   <summary>
		///  找回缓存参数集合
		///   </summary>
		///   <param name="cacheKey"> 用于找回参数的关键字 </param>
		///   <returns> 缓存的参数集合 </returns>
		public static MySqlParameter[] GetCachedParameters(string cacheKey)
		{
			MySqlParameter[] cachedParms = (MySqlParameter[])parmCache[cacheKey];

			if (cachedParms == null)
				return null;

			MySqlParameter[] clonedParms = new MySqlParameter[cachedParms.Length];

			for (int i = 0, j = cachedParms.Length; i < j; i++)
				clonedParms[i] = (MySqlParameter)((ICloneable)cachedParms[i]).Clone();

			return clonedParms;
		}

		///   <summary>
		///  准备执行一个命令
		///   </summary>
		///   <param name="cmd"> sql命令 </param>
		///   <param name="conn"> OleDb连接 </param>
		///   <param name="trans"> OleDb事务 </param>
		///   <param name="cmdType"> 命令类型例如 存储过程或者文本 </param>
		///   <param name="cmdText"> 命令文本,例如:Select * from Products </param>
		///   <param name="cmdParms"> 执行命令的参数 </param>
		private static void PrepareCommand(MySqlCommand cmd, MySqlConnection conn, MySqlTransaction trans, CommandType cmdType, string cmdText, MySqlParameter[] cmdParms)
		{
			if (conn.State != ConnectionState.Open)
				conn.Open();

			cmd.Connection = conn;
			cmd.CommandText = cmdText;

			if (trans != null)
				cmd.Transaction = trans;

			cmd.CommandType = cmdType;

			if (cmdParms != null)
			{
				foreach (MySqlParameter parm in cmdParms)
					cmd.Parameters.Add(parm);
			}
		}

		/// <summary>
		/// 增加cellinfo
		/// </summary>
		/// <param name="cellinfo"></param>
		public static bool Add_CellInfo(List<cell_info> cellinfo, string timestr)
		{
			if (cellinfo.Count < 1)
			{
				LogRecord.WriteErrorLog("添加cellinfo失败！,报错信息: 数据为空！");
				return false;
			}

			string time = DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss");
			StringBuilder sb = new StringBuilder();

			sb.Append("INSERT INTO cellinfo (lotid,cellid,lineid,img,imgcenter,state,workmode,facemode,createtime) values ");
			int i = 0;
			foreach (var item in cellinfo)
			{
				if (i > 0)
				{
					sb.Append(",");
				}
				string str = string.Format("('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}')", item.lotid, item.cellid, item.lineid, item.img, item.imgcenter, item.state, item.workmode, item.facename, timestr);
				sb.Append(str);
				i++;
			}
			sb.Replace("\\", "\\\\");
			string sql = sb.ToString();
			try
			{
				//!=0 表示执行成功
				if (ExecuteNonQuery(Conn, CommandType.Text, sql) != 0)
				{
					Console.WriteLine("添加cellinfo成功！");
					LogRecord.WriteMesLog("数据", "添加cellinfo成功！");
					return true;
				}
				else
				{
					Console.WriteLine("添加记录失败！");
					LogRecord.WriteMesLog("数据", "添加cellinfo失败！");
					return false;
				}
			}
			catch (Exception ex)
			{
				Console.WriteLine("添加cellinfo-sql抛出异常！");
				LogRecord.WriteErrorLog("添加cellinfo失败！ sql：" + sql + ",报错信息:" + ex.ToString());
				return false;
			}
		}

		/// <summary>
		/// 增加cellinfo
		/// </summary>
		/// <param name="cellinfo"></param>
		public static bool Add_CellInfoDetail(List<CellInfoDetail> cellinfo, string timestr)
		{
			if (cellinfo.Count < 1)
			{
				LogRecord.WriteErrorLog("添加cellinfo失败！,报错信息: 数据为空！");
				return false;
			}

			string time = DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss");
			StringBuilder sb = new StringBuilder();

			sb.Append("INSERT INTO cellinfo_detail (lotid,cellid,lineid,img,imgcenter,state,workmode,facemode,createtime,modelName,userCode,damaPici,liuShui,damaVer,rectErrs) values ");
			int i = 0;
			foreach (var item in cellinfo)
			{
				if (i > 0)
				{
					sb.Append(",");
				}
				string str = $@"('{item.lotid}','{item.cellid}','{item.lineid}','{item.img}','{item.imgcenter}','{item.state}','{item.workmode}','{item.facename}','{timestr}','{item.modelName}','{item.userCode}','{item.damaPici}','{item.liuShui}','{item.damaVer}','{item.rectErrs}')";

				//, item.lotid, item.cellid, item.lineid, item.img, item.imgcenter, item.state, item.workmode, item.facename, timestr);

				//string str = string.Format("('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}')", item.lotid, item.cellid, item.lineid, item.img, item.imgcenter, item.state, item.workmode, item.facename, timestr);
				sb.Append(str);
				i++;
			}
			sb.Replace("\\", "\\\\");
			string sql = sb.ToString();
			try
			{
				//!=0 表示执行成功
				if (ExecuteNonQuery(Conn, CommandType.Text, sql) != 0)
				{
					Console.WriteLine("添加cellinfo_detail成功！");
					LogRecord.WriteMesLog("数据", $"添加cellinfo_detail成功！{sql}");
					return true;
				}
				else
				{
					Console.WriteLine("添加记录失败！");
					LogRecord.WriteMesLog("数据", "添加cellinfo_detail失败！");
					return false;
				}
			}
			catch (Exception ex)
			{
				Console.WriteLine("添加cellinfo_detail-sql抛出异常！");
				LogRecord.WriteErrorLog("添加cellinfo_detail失败！ sql：" + sql + ",报错信息:" + ex.ToString());
				return false;
			}
		}

		/// <summary>
		/// 查询cellinfo
		/// </summary>
		/// <param name="cellinfo"></param>
		public static DataTable Get_CellInfo(string start, string end, string columStr, string findStr)
		{
			string sql;
			StringBuilder sb = new StringBuilder();
			if (string.IsNullOrEmpty(columStr))
			{
				sb.Append("SELECT id,lotid,cellid,lineid,img,imgcenter,state,workmode,facemode,createtimeFROM cellinfo WHERE createtime BETWEEN '{0}' AND '{1}' ORDER BY createtime ASC");
				sql = string.Format(sb.ToString(), start, end);
			}
			else
			{
				sb.Append("SELECT id,lotid,cellid,lineid,img,imgcenter,state,workmode,facemode,createtime FROM cellinfo WHERE createtime BETWEEN '{0}' AND '{1}' AND {2}='{3}'  ORDER BY createtime ASC");
				sql = string.Format(sb.ToString(), start, end, columStr, findStr);
			}
			try
			{
				DataTable dataTable = GetDataTable(Conn, CommandType.Text, sql);
				dataTable.Columns["lotid"].ColumnName = "产品号";
				dataTable.Columns["cellid"].ColumnName = "  CellID";
				dataTable.Columns["img"].ColumnName = "图片路径";
				dataTable.Columns["imgcenter"].ColumnName = "中心图片路径";
				dataTable.Columns["state"].ColumnName = "cell状态";
				dataTable.Columns["createtime"].ColumnName = "时间";
				dataTable.Columns["lineid"].ColumnName = "搬运编号";
				dataTable.Columns["workmode"].ColumnName = "检测模式";
				dataTable.Columns["facemode"].ColumnName = "检测面";
				dataTable.Columns["id"].ColumnName = "数据序号";
				//

				//dataTable.Columns["img"].ColumnMapping = MappingType.Hidden;//隐藏列
				//dataTable.Columns["imgc"].ColumnMapping = MappingType.Hidden;//隐藏列
				//dataTable.Columns["id"].ColumnMapping = MappingType.Hidden;//隐藏列

				if (dataTable.Rows.Count > 0)
				{
					foreach (DataRow row in dataTable.Rows)
					{
						//row["Result"] = row["Result"].ToString() == "1" ? "良" : "不良";
					}
				}
				LogRecord.WriteMesLog("查询cellinfo", "查询成功！sql: " + sql);
				return dataTable;
			}
			catch (Exception ex)
			{
				LogRecord.WriteErrorLog("查询cellinfo失败！ sql：" + sql + ",报错信息:" + ex.ToString());
				return null;
			}
		}

		/// <summary>
		/// 获取昨天的数据
		/// </summary>
		/// <param name="cellinfo"></param>
		public static DataTable Get_CellInfoYestoday()
		{
			string sql;
			StringBuilder sb = new StringBuilder();
			sb.Append("SELECT id,lotid,cellid,lineid,img,imgcenter,state,workmode,facemode,createtime FROM cellinfo WHERE DATEDIFF(NOW(),createtime) > 0 ORDER BY createtime ASC");
			sql = sb.ToString();
			try
			{
				DataTable dataTable = GetDataTable(Conn, CommandType.Text, sql);
				//dataTable.Columns["lotid"].ColumnName = "产品号";
				//dataTable.Columns["cellid"].ColumnName = "  CellID";
				//dataTable.Columns["img"].ColumnName = "图片路径";
				//dataTable.Columns["imgcenter"].ColumnName = "中心图片路径";
				//dataTable.Columns["state"].ColumnName = "cell状态";
				//dataTable.Columns["createtime"].ColumnName = "时间";
				//dataTable.Columns["lineid"].ColumnName = "搬运编号";
				//dataTable.Columns["workmode"].ColumnName = "检测模式";
				//dataTable.Columns["facemode"].ColumnName = "检测面";
				//dataTable.Columns["id"].ColumnName = "数据序号";
				//dataTable.Columns["img"].ColumnMapping = MappingType.Hidden;//隐藏列
				//dataTable.Columns["imgc"].ColumnMapping = MappingType.Hidden;//隐藏列
				//dataTable.Columns["id"].ColumnMapping = MappingType.Hidden;//隐藏列

				LogRecord.WriteMesLog("查询cellinfo昨天数据", "查询成功！sql: " + sql);
				return dataTable;
			}
			catch (Exception ex)
			{
				LogRecord.WriteErrorLog("查询cellinfo昨天数据失败！ sql：" + sql + ",报错信息:" + ex.ToString());
				return null;
			}
		}

		/// <summary>
		/// 删除昨天的cellinfo数据
		/// </summary>
		public static bool Del_CellInfoYestoday()
		{
			string sql;
			StringBuilder sb = new StringBuilder();
			sb.Append("DELETE FROM cellinfo WHERE DATEDIFF(NOW(),createtime) > 0");
			sql = sb.ToString();
			try
			{
				if (ExecuteNonQuery(Conn, CommandType.Text, sql) != 0)
				{
					Console.WriteLine("CellInfo昨天数据删除数据成功！");
					LogRecord.WriteMesLog("数据", "CellInfo昨天数据删除数据成功！");
					return true;
				}
			}
			catch (Exception ex)
			{
				LogRecord.WriteErrorLog("删除cellinfo昨天数据失败！ sql：" + sql + ",报错信息:" + ex.ToString());
				return false;
			}
			return false;
		}

		/// <summary>
		/// 增加生产报告
		/// </summary>
		/// <param name="Report"></param>
		public static bool Add_Report(string TotalNumber, string NGNumber, string OKNumber)
		{
			string sql = "INSERT INTO report (TotalNumber,NGNumber,OKNumber,ReportDateTime) values ('{0}','{1}','{2}','{3}')";
			sql = string.Format(sql, TotalNumber, NGNumber, OKNumber, DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss"));
			try
			{
				//!=0 表示执行成功
				if (ExecuteNonQuery(Conn, CommandType.Text, sql) != 0)
				{
					Console.WriteLine("添加生产报告成功！");
					LogRecord.WriteMesLog("数据", "添加生产记录成功！");
					return true;
				}
				else
				{
					Console.WriteLine("添加生产报告失败！");
					LogRecord.WriteMesLog("数据", "添加生产报告失败！");
					return false;
				}
			}
			catch (Exception ex)
			{
				Console.WriteLine("添加生产报告-sql抛出异常！");
				LogRecord.WriteErrorLog("添加生产报告失败！ sql：" + sql + ",报错信息:" + ex.ToString());
				return false;
			}
		}

		/// <summary>
		/// 增加生产记录
		/// </summary>
		/// <param name="Add_Product"></param>
		public static bool Add_Product(List<product> product)
		{
			if (product.Count < 1)
			{
				LogRecord.WriteErrorLog("添加cellinfo失败！,报错信息: 数据为空！");
				return false;
			}

			string time = DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss");
			StringBuilder sb = new StringBuilder();

			sb.Append("INSERT INTO product (lotid,total,ok,ng,state,img,createtime,workmode,okA,ngA,okB,ngB,aones,atwos,bones,btwos) values ");
			int i = 0;
			foreach (var item in product)
			{
				if (i > 0)
				{
					sb.Append(",");
				}
				string str = string.Format("('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}')", item.lotid, item.total, item.ok, item.ng, item.state, item.img, time, item.workmode, item.okA, item.ngA, item.okB, item.ngB, item.aones, item.atwos, item.bones, item.btwos);
				sb.Append(str);
				i++;
			}
			string sql = sb.ToString();
			try
			{
				//!=0 表示执行成功
				if (ExecuteNonQuery(Conn, CommandType.Text, sql) != 0)
				{
					Console.WriteLine("添加生产记录成功！");
					LogRecord.WriteMesLog("数据", "添加生产记录成功！sql:" + sql);
					return true;
				}
				else
				{
					Console.WriteLine("添加记录失败！");
					LogRecord.WriteMesLog("数据", "添加生产记录失败！sql:" + sql);
					return false;
				}
			}
			catch (Exception ex)
			{
				Console.WriteLine("添加生产记录-sql抛出异常！");
				LogRecord.WriteErrorLog("添加生产记录失败！ sql：" + sql + ",报错信息:" + ex.ToString());
				return false;
			}
		}

		/// <summary>
		/// 增加生产记录
		/// </summary>
		/// <param name="Add_Product"></param>
		public static bool Add_Product_Detail(List<ProductDetail> product)
		{
			if (product.Count < 1)
			{
				LogRecord.WriteErrorLog("添加product_detail失败！,报错信息: 数据为空！");
				return false;
			}

			string time = DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss");
			StringBuilder sb = new StringBuilder();

			sb.Append(@"INSERT INTO product_detail (lotid,total,ok,ng,state,img,createtime,workmode,okA,ngA,okB,ngB,aones,atwos,bones,btwos,modelName,userCode,damaPici,liuShui,damaVer)
							values ");
			int i = 0;
			foreach (var item in product)
			{
				if (i > 0)
				{
					sb.Append(",");
				}
				var str = $@"('{item.lotid}', '{item.total}', '{item.ok}', '{item.ng}', '{item.state}', '{item.img}', '{time}', '{item.workmode}', '{item.okA}', '{item.ngA}', '{item.okB}', '{item.ngB}', '{item.aones}', '{item.atwos}', '{item.bones}', '{item.btwos}','{item.modelName}','{item.userCode}','{item.damaPici}','{item.liuShui}','{item.damaVer}')";
				//string str = string.Format("('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}')", item.lotid, item.total, item.ok, item.ng, item.state, item.img, time, item.workmode, item.okA, item.ngA, item.okB, item.ngB, item.aones, item.atwos, item.bones, item.btwos);
				sb.Append(str);
				i++;
			}
			string sql = sb.ToString();
			try
			{
				//!=0 表示执行成功
				if (ExecuteNonQuery(Conn, CommandType.Text, sql) != 0)
				{
					Console.WriteLine("添加生产详细成功！");
					LogRecord.WriteMesLog("数据", "添加生产详细成功！sql:" + sql);
					return true;
				}
				else
				{
					Console.WriteLine("添加详细失败！");
					LogRecord.WriteMesLog("数据", "添加生产详细失败！sql:" + sql);
					return false;
				}
			}
			catch (Exception ex)
			{
				Console.WriteLine("添加生产详细-sql抛出异常！");
				LogRecord.WriteErrorLog("添加生产详细失败！ sql：" + sql + ",报错信息:" + ex.ToString());
				return false;
			}
		}

		/// <summary>
		/// 查询Product
		/// </summary>
		/// <param name="Get_Product"></param>
		public static DataTable Get_Product(string start, string end, string columStr, string findStr, string coder)
		{
			string sql;
			StringBuilder sb = new StringBuilder();
			if (string.IsNullOrEmpty(columStr))
			{
				if (string.IsNullOrEmpty(coder))
				{
					sb.Append("SELECT id,lotid,total,ok,ng,state,img,createtime,workmode,okA,ngA,okB,ngB,aones,atwos,bones,btwos FROM product WHERE createtime BETWEEN '{0}' AND '{1}' ORDER BY createtime ASC");
					sql = string.Format(sb.ToString(), start, end);
				}
				else
				{
					sb.Append("SELECT id,lotid,total,ok,ng,state,img,createtime,workmode,okA,ngA,okB,ngB,aones,atwos,bones,btwos FROM product WHERE createtime BETWEEN '{0}' AND '{1}' AND  lotid='{2}'  ORDER BY createtime ASC");
					sql = string.Format(sb.ToString(), start, end, coder);
				}
			}
			else
			{
				if (string.IsNullOrEmpty(coder))
				{
					sb.Append("SELECT id,lotid,total,ok,ng,state,workmode,okA,aones,atwos,ngA,okB,bones,btwos,ngB,createtime FROM product WHERE createtime BETWEEN '{0}' AND '{1}' AND {2}='{3}'  ORDER BY createtime ASC");
					sql = string.Format(sb.ToString(), start, end, columStr, findStr);
				}
				else
				{
					sb.Append("SELECT id,lotid,total,ok,ng,state,workmode,okA,aones,atwos,ngA,okB,bones,btwos,ngB,createtime FROM product WHERE createtime BETWEEN '{0}' AND '{1}' AND {2}='{3}' AND  lotid='{4}'  ORDER BY createtime ASC");

					sql = string.Format(sb.ToString(), start, end, columStr, findStr, coder);
				}
			}
			try
			{
				DataTable dataTable = GetDataTable(Conn, CommandType.Text, sql);
				dataTable.Columns["lotid"].ColumnName = "产品号";
				dataTable.Columns["total"].ColumnName = "CELL总数";
				dataTable.Columns["ok"].ColumnName = "OK总数";
				dataTable.Columns["ng"].ColumnName = "NG总数";
				dataTable.Columns["state"].ColumnName = "状态";
				dataTable.Columns["workmode"].ColumnName = "检测模式";

				dataTable.Columns["okA"].ColumnName = "A面OK";
				dataTable.Columns["ngA"].ColumnName = "A面NG";
				dataTable.Columns["okB"].ColumnName = "B面OK";
				dataTable.Columns["ngB"].ColumnName = "B面NG";
				//
				dataTable.Columns["id"].ColumnName = "数据序号";

				//
				dataTable.Columns["aones"].ColumnName = "A面1次OK";
				dataTable.Columns["atwos"].ColumnName = "A面2次OK";
				dataTable.Columns["bones"].ColumnName = "B面1次OK";
				dataTable.Columns["btwos"].ColumnName = "B面2次OK";

				dataTable.Columns["createtime"].ColumnName = "时间";

				LogRecord.WriteMesLog("查询Product", "查询成功！sql: " + sql);
				return dataTable;
			}
			catch (Exception ex)
			{
				LogRecord.WriteErrorLog("查询Product失败！ sql：" + sql + ",报错信息:" + ex.ToString());
				return null;
			}
		}

		/// <summary>
		/// 增加日志记录
		/// </summary>
		/// <param name="LogType"></param>
		public static bool Add_Record(string LogType, string LogInfo, string User, string Status)
		{
			string sql = "INSERT INTO syslog (LogType,LogInfo,User,Status,LogTime) values ('{0}','{1}','{2}','{3}','{4}')";
			sql = string.Format(sql, LogType, LogInfo, User, Status, DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss"));
			try
			{
				//!=0 表示执行成功
				if (ExecuteNonQuery(Conn, CommandType.Text, sql) != 0)
				{
					Console.WriteLine("添加日志记录成功！");
					LogRecord.WriteMesLog("数据", "添加日志记录成功！");
					return true;
				}
				else
				{
					Console.WriteLine("添加日志记录失败！");
					LogRecord.WriteMesLog("数据", "添加日志记录失败！");
					return false;
				}
			}
			catch (Exception ex)
			{
				Console.WriteLine("添加日志记录-sql抛出异常！");
				LogRecord.WriteErrorLog("添加日志记录失败！ sql：" + sql + ",报错信息:" + ex.ToString());
				return false;
			}
		}

		/// <summary>
		/// 增加用户设置权限
		/// </summary>
		/// <param name="LogType"></param>
		public static bool Add_User(string LoginName, string LoginPwd, bool DamaCtrl, bool DumaCtrl, bool SysSet, bool SysLog, bool Report, bool Check, bool UserManage, bool BiaoCtrl, bool PeiCtrl, bool Status, bool diy)
		{
			string sql = "INSERT INTO sysadmins (LoginName,LoginPwd,DamaCtrl,DumaCtrl,SysSet,SysLog,Report,Check_,UserManage,BiaoCtrl,PeiCtrl,Status_,diy) values ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}')";
			sql = string.Format(sql, LoginName, LoginPwd, DamaCtrl ? 1 : 0, DumaCtrl ? 1 : 0, SysSet ? 1 : 0, SysLog ? 1 : 0, Report ? 1 : 0, Check ? 1 : 0, UserManage ? 1 : 0, BiaoCtrl ? 1 : 0, PeiCtrl ? 1 : 0, Status ? 0 : 1, diy ? 0 : 1);
			try
			{
				//!=0 表示执行成功
				if (ExecuteNonQuery(Conn, CommandType.Text, sql) != 0)
				{
					Console.WriteLine("添加用户成功！");
					LogRecord.WriteMesLog("数据", "添加用户成功！");
					return true;
				}
				else
				{
					Console.WriteLine("添加用户失败！sql: " + sql);
					LogRecord.WriteMesLog("数据", "添加用户失败！");
					return false;
				}
			}
			catch (Exception ex)
			{
				Console.WriteLine("添加用户-sql抛出异常！");
				LogRecord.WriteErrorLog("添加用户失败！ sql：" + sql + ",报错信息:" + ex.ToString());
				return false;
			}
		}

		/// <summary>
		/// 修改用户设置权限
		/// </summary>
		/// <param name="LoginName"></param>

		public static bool Update_User(string LoginName, bool DamaCtrl, bool DumaCtrl, bool SysSet, bool SysLog, bool Report, bool Check, bool UserManage, bool BiaoCtrl, bool PeiCtrl, bool Status, bool diy)
		{
			string sql = "UPDATE sysadmins SET DamaCtrl = {0}";
			sql += " , DumaCtrl = {1}";
			sql += " , SysSet = {2}";
			sql += " , SysLog = {3}";
			sql += " , Report = {4}";
			sql += " , Check_ = {5}";
			sql += " , UserManage = {6}";
			sql += " , BiaoCtrl = {7}";
			sql += " , PeiCtrl = {8}";
			sql += " , Status_ = {9}";
			sql += " , diy = {10}";
			sql += " WHERE LoginName = '{11}'";

			sql = string.Format(sql, DamaCtrl ? 1 : 0, DumaCtrl ? 1 : 0, SysSet ? 1 : 0, SysLog ? 1 : 0, Report ? 1 : 0, Check ? 1 : 0, UserManage ? 1 : 0, BiaoCtrl ? 1 : 0, PeiCtrl ? 1 : 0, Status ? 0 : 1, diy ? 1 : 0, LoginName);
			try
			{
				int affectedRows = ExecuteNonQuery(Conn, CommandType.Text, sql);

				if (affectedRows > 0)
				{
					LogRecord.WriteMesLog("数据", "修改用户权限成功！");
					return true;
				}
				else
				{
					Console.WriteLine("修改用户权限失败！");
					LogRecord.WriteMesLog("数据", "修改用户权限失败！");
					return false;
				}
			}
			catch (Exception ex)
			{
				Console.WriteLine("修改用户权限-sql抛出异常！");
				LogRecord.WriteErrorLog("修改用户权限失败！ sql：" + sql + ",报错信息:" + ex.ToString());
				return false;
			}
		}

		/// <summary>
		/// 修改用户密码权限
		/// </summary>
		/// <param name="LoginName"></param>

		public static bool Update_PassWord(string LoginName, string LoginPwd)
		{
			string sql = "UPDATE sysadmins SET LoginPwd = '{0}'";
			sql += " WHERE LoginName = '{1}'";

			sql = string.Format(sql, LoginPwd, LoginName);
			try
			{
				int affectedRows = ExecuteNonQuery(Conn, CommandType.Text, sql);

				if (affectedRows > 0)
				{
					LogRecord.WriteMesLog("数据", "修改用户密码成功！");
					return true;
				}
				else
				{
					Console.WriteLine("修改用户密码失败！");
					LogRecord.WriteMesLog("数据", "修改用户密码失败！");
					return false;
				}
			}
			catch (Exception ex)
			{
				Console.WriteLine("修改用户密码-sql抛出异常！");
				LogRecord.WriteErrorLog("修改用户密码失败！ sql：" + sql + ",报错信息:" + ex.ToString());
				return false;
			}
		}

		/// <summary>
		/// 删除用户
		/// </summary>
		public static bool Delete_User(string str)
		{
			string sql = "DELETE FROM sysadmins  WHERE LoginName =  '{0}'";
			sql = string.Format(sql, str);
			try
			{
				int affectedRows = ExecuteNonQuery(Conn, CommandType.Text, sql);

				if (affectedRows > 0)
				{
					LogRecord.WriteMesLog("数据", "删除用户成功！");
					return true;
				}
				else
				{
					Console.WriteLine("修改用户密码失败！");
					LogRecord.WriteMesLog("数据", "删除用户失败！");
					return false;
				}
			}
			catch (Exception ex)
			{
				Console.WriteLine("删除用户-sql抛出异常！");
				LogRecord.WriteErrorLog("删除用户失败！ sql：" + sql + ",报错信息:" + ex.ToString());
				return false;
			}
		}
	}
}